CREATE PROCEDURE sp_asi_UpdateNameIndex
     @id varchar(10),            -- Name.ID
     @fieldName varchar(61),            -- <Table>.<FIELD>
     @fieldValue varchar(8000) = NULL,
     @indexName varchar(31) = NULL
AS
     if @indexName is null
     begin
         select @indexName = (CASE WHEN ParameterName = 'Member_Control.Index1Fld' THEN '1'
                       WHEN ParameterName = 'Member_Control.Index2Fld' THEN '2'
                       WHEN ParameterName = 'Member_Control.Index3Fld' THEN '3'
                       WHEN ParameterName = 'Member_Control.Index4Fld' THEN '4'
                      END)
         from System_Params where ParameterName like 'Member_Control.Index%Fld' and ShortValue = @fieldName
     end
     if @indexName is not null
     begin
         -- is this a delete
         if @fieldValue is null
         begin
             delete from Name_Indexes where ID = @id and INDEX_NAME = @indexName
         end
         else
         begin
             -- Insert or update
             -- Field is an index; check to see if it's a UD multiselect field
             declare @udms int
             set @udms = 0
             select @udms = 1 where exists (select * from UD_Field where TABLE_NAME + '.' + FIELD_NAME = @fieldName and MULTI_SELECT = 1)
             if @udms = 0
             begin
                 -- is not a UD MS field; simple insert/update
                 update Name_Indexes
                 set INDEX_VALUE = UPPER(LEFT(@fieldValue, 30))
                 where ID = @id
                 and INDEX_NAME = @indexName
                 if @@ROWCOUNT = 0
                     insert into Name_Indexes (ID, INDEX_NAME, INDEX_VALUE, QUANTITY) values (@id, @indexName, UPPER(LEFT(@fieldValue, 30)), 0)
             end
             else
             begin
                 -- Is a multiselect field
                 -- Delete existing index values
                 delete from Name_Indexes where ID = @id and INDEX_NAME = @indexName
                 -- Split on commas and insert new index values
                 declare @idx int
                 declare @value varchar(30)
                 set @idx = CHARINDEX(',', @fieldValue, 0)
                 while @idx > 0
                 begin
                     set @value = UPPER(LEFT(SUBSTRING(@fieldValue, 1, @idx - 1), 30))
                     insert into Name_Indexes (ID, INDEX_NAME, INDEX_VALUE, QUANTITY) values (@id, @indexName, @value, 0)
                     set @fieldValue = SUBSTRING(@fieldValue, @idx + 1, LEN(@fieldValue) - @idx)
                     set @idx = CHARINDEX(',', @fieldValue, 0)
                 end
                 insert into Name_Indexes (ID, INDEX_NAME, INDEX_VALUE, QUANTITY) values (@id, @indexName, UPPER(LEFT(@fieldValue, 30)), 0)
             end
         end
     end

GO
GRANT EXECUTE ON  [dbo].[sp_asi_UpdateNameIndex] TO [IMIS]
GO